/**
 * User数据模型模块
 * model\sqlite\user.js
 */

//导入SQLite3模块
const sqlite3 = require("sqlite3").verbose();
//导入工具模块
const util = require("../../common/util");
//导入配置模块
const config = require("../../common/config");

class UserDB {
   /**
  * @constructor
  * @private
  */
   constructor() {
      this.dbFile = config.dbFile;
      this.instance = null;
      this.db = null;
      this.register = this.add;
   }

   /**
    * 创建UserDB对象
    * @returns {UserDB} UserDB实例
    */
   static getInstance() {
      if (!this.instance) {
         this.instance = new UserDB();
      }
      return this.instance;
   }

   /**
    * 连接数据库
    * @returns {Promise}
    */
   connect() {
      return new Promise((resolve, reject) => {
         this.db = new sqlite3.Database(this.dbFile, (err) => {
            if (err) {
               reject(err);
            } else {
               resolve("connect ok.");
            }
         });
      });
   }
   /**
    * 关闭数据库
    * @returns  {Promise}
    */
   close() {
      return new Promise((resolve, reject) => {
         this.db.close((err) => {
            if (err) {
               reject(err);
            } else {
               resolve("close ok.");
            }
         });
      });
   }

   /**
    * 获取指定ID的书籍信息
    * @param {Number} userId 书籍ID
    * @returns  {Promise}
    */
   find(userId) {
      return new Promise((resolve, reject) => {
         let sql = "SELECT * FROM users WHERE id = ?";
         let params = [userId];
         this.db.get(sql, params, (err, result) => {
            if (err) {
               util.log(err);
               reject(err);
            } else {
               resolve(result);
            }
         });
      });
   }

   /**
    * 获取用户信息列表
    * @param {Number} limit 数量
    * @param {Number} offset 开始
    * @returns  {Promise}
    */
   findAll(limit = -1, offset = -1) {
      return new Promise((resolve, reject) => {
         let sql = "";
         let params = [];
         if (limit === -1) {
            sql = "SELECT * FROM users ORDER BY id";
         } else if (offset === -1) {
            sql = "SELECT * FROM users ORDER BY id LIMIT ?";
            params[0] = limit;
         } else {
            sql = "SELECT * FROM users ORDER BY id LIMIT ? OFFSET ?";
            params[0] = limit;
            params[1] = offset;
         }
         this.db.all(sql, params, (err, result) => {
            if (err) {
               reject(err);
            } else {
               resolve(result);
            }
         });
      });
   }

   /**
    * 新增用户
    * @param {Object} user 用户数据
    * @returns  {Promise}
    */
   add(user) {
      return new Promise((resolve, reject) => {
         let sql = `INSERT INTO users (
      username, password, nickname, truename, avatar,
      role, last_login_time, last_login_ip, created_time, created_ip, 
      updated_time
   ) VALUES (
      ?, ?, ?, ?, ?,
      ?, ?, ?, ?, ?,
      ?
   );`;
         let params = [
            user.username,
            user.password,
            user.nickname,
            user.truename,
            user.avatar,
            user.role,
            user.lastLoginTime,
            user.lastLoginIP,
            user.createdTime,
            user.createdIP,
            user.updatedTime,
         ];

         this.db.run(sql, params, function (err, result) {
            if (err) {
               reject(err);
            } else {
               util.log(this);
               resolve(this.lastID); //插入的数据的自增ID
            }
         });
      });
   }

   /**
    * 修改用户
    * @param {Object} user 用户数据
    * @returns  {Promise}
    */
   update(user) {
      return new Promise((resolve, reject) => {
         let sql = `UPDATE users SET 
    username = ?, password = ?, nickname = ?, truename = ?, 
    avatar = ?, role = ?, updated_time = ?
    WHERE id = ?
    ;`;
         let params = [
            user.username,
            user.password,
            user.nickname,
            user.truename,
            user.avatar,
            user.role,
            user.updatedTime,
            user.id,
         ];

         this.db.run(sql, params, function (err, result) {
            if (err) {
               reject(err);
            } else {
               resolve(this.changes); //影响的记录数
            }
         });
      });
   }

   /**
    * 删除用户
    * @param {Number} userId 用户ID
    * @returns  {Promise}
    */
   remove(userId) {
      return new Promise((resolve, reject) => {
         let sql = "DELETE FROM users WHERE id = ?";
         let params = [userId];
         this.db.run(sql, params, function (err, result) {
            if (err) {
               reject(err);
            } else {
               resolve(this.changes); //影响的记录数
            }
         });
      });
   }

   /**
 * 获取指定ID的书籍信息
 * @param {String} username 用户名
 * @param {String} password 密码
 * @returns  {Promise}
 */
   login(username, password) {
      return new Promise((resolve, reject) => {
         let sql = "SELECT * FROM users WHERE username = ? AND password = ?";
         let params = [username, password];
         this.db.get(sql, params, (err, result) => {
            if (err) {
               util.err(err);
               reject(err);
            } else {
               resolve(result);
            }
         });
      });
   }

   /**
* 更新用户登录相关数据
* @param {String} username 用户名
* @param {Object} lastLogin 登录状态对象 
* @returns {Promise}
*/
   touch(username, lastLogin) {
      return new Promise((resolve, reject) => {
         let sql = `UPDATE users SET 
         last_login_time = ? , last_login_ip = ?, 
         login_count = login_count + 1 
         WHERE username = ?`;
         let params = [lastLogin.time, lastLogin.ip, username];
         this.db.run(sql, params, (err, result) => {
            if (err) {
               util.err(err);
               reject(err);
            } else {
               resolve(this.changes);
            }
         });
      });

   }

   /**
    * 获取用户数量
    * @returns {Promise}
    */
   getCount() {
      return new Promise((resolve, reject) => {
         let sql = "SELECT count(*) AS total FROM users";
         let params = [];
         this.db.get(sql, params, function (err, result) {
            if (err) {
               reject(err);
            } else {
               resolve(result); //影响的记录数
            }
         });
      });
   }

   /**
  * 搜索用户
  * @param {String} q 查询关键字
  * @param {Number} limit 数量
  * @param {Number} offset 偏移
  * @returns {Promise} 
  */
   search(q, limit = -1, offset = -1) {
      return new Promise((resolve, reject) => {
         let sql = "";
         q = `%${q}%`;
         let params = [q, q];
         if (limit === -1) {
            sql = "SELECT * FROM users WHERE username LIKE ? OR nickname LIKE ?";
         } else if (offset === -1) {
            sql = "SELECT * FROM users WHERE username LIKE ? OR nickname LIKE ?";
            params.push(limit);
         } else {
            sql = "SELECT * FROM users  WHERE username LIKE ? OR nickname LIKE ?";
            params.push(limit);
            params.push(offset);
         }
         this.db.all(sql, params, (err, result) => {
            if (err) {
               util.err(err);
               reject(err);
            } else {
               resolve(result);
            }
         });
      });
   }

   /**
  * 修改用户个人信息
  * @param {Object} user 用户数据
  * @returns  {Promise}
  */
   changeInfo(id, info) {
      return new Promise((resolve, reject) => {
         let sql = `UPDATE users SET nickname = ?, truename = ?,  avatar = ? WHERE id = ?;`;
         let params = [
            info.nickname,
            info.truename,
            info.avatar,
            id,
         ];

         this.db.run(sql, params, function (err, result) {
            if (err) {
               util.err(err);
               reject(err);
            } else {
               resolve(this.changes); //影响的记录数
            }
         });
      });
   }

   /**
* 修改用户密码
* @param {Number} id 用户id
* @param {String} password 用户密码
* @returns  {Promise}
*/
   changePassword(id, password) {
      return new Promise((resolve, reject) => {
         let sql = `UPDATE users SET password = ? WHERE id = ?;`;
         let params = [password, id];

         this.db.run(sql, params, function (err, result) {
            if (err) {
               util.err(err);
               reject(err);
            } else {
               resolve(this.changes); //影响的记录数
            }
         });
      });
   }

}

module.exports = UserDB;
